/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.HotelDTO;
import east2west.interfaces.HotelInterface;
import east2west.util.DatabaseUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author TUNGTRUM
 */
public class HotelDAO implements HotelInterface {

    Connection con = null;
    HotelDTO hotel = null;
    CallableStatement call = null;
    ResultSet rs = null;

    public ArrayList<HotelDTO> readByAll() {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT *FROM V_Hotels");
            rs = call.executeQuery();
            ArrayList<HotelDTO> list = new ArrayList<HotelDTO>();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));

                list.add(hotel);
            }

            return list;
        } catch (SQLException ex) {

            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<HotelDTO> readByTopHotesls() {
        ArrayList<HotelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT Top(8) * FROM V_Hotels Order by HotelId DESC");
            rs = call.executeQuery();
            list = new ArrayList<HotelDTO>();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));
                list.add(hotel);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public HotelDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Hotels WHERE HotelId = ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));
            }
            rs.close();
            call.close();
            return hotel;
        } catch (SQLException ex) {
            //Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<HotelDTO> findByRate(int rate) {
        ArrayList<HotelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Hotels WHERE Rate = ?");
            call.setInt(6, rate);

            rs = call.executeQuery();
            list = new ArrayList<HotelDTO>();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));
                list.add(hotel);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            //Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<HotelDTO> findByCity(String city) {
        ArrayList<HotelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Hotels WHERE City = ?");
            call.setString(9, city);

            rs = call.executeQuery();
            list = new ArrayList<HotelDTO>();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));
                list.add(hotel);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            // Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<HotelDTO> findByName(String name) {
        ArrayList<HotelDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT * FROM V_Hotels WHERE HotelName = ?");
            call.setString(2, name);

            rs = call.executeQuery();
            list = new ArrayList<HotelDTO>();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));
                list.add(hotel);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {
            //Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<HotelDTO> findInfo(HotelDTO dto) {

        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Search_Hotels(?)}");
            call.setString(1, dto.getCity());
            rs = call.executeQuery();
            ArrayList<HotelDTO> list = new ArrayList<HotelDTO>();
            while (rs.next()) {
              HotelDTO  hotel1 = new HotelDTO();
                hotel1.setHotelId(rs.getInt(1));
                hotel1.setHotelName(rs.getString(2));
                hotel1.setEmail(rs.getString(3));
                hotel1.setWebsite(rs.getString(4));
                hotel1.setImagesUrl(rs.getString(5));
                hotel1.setRate(rs.getInt(6));
                hotel1.setAddress(rs.getString(7));
                hotel1.setPhone(rs.getString(8));
                hotel1.setCity(rs.getString(9));
                hotel1.setDescription(rs.getString(10));
                list.add(hotel1);
            }

            return list;
        } catch (SQLException ex) {

            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public int create(HotelDTO hotel) {
        try {
            con = DatabaseUtil.getConnection();

            call = con.prepareCall("{call sp_Insert_Hotels(?,?,?,?,?,?,?,?,?)}");
            call.setString(1, hotel.getHotelName());
            call.setString(2, hotel.getEmail());
            call.setString(3, hotel.getWebsite());
            call.setString(4, hotel.getImagesUrl());
            call.setInt(5, hotel.getRate());
            call.setString(6, hotel.getAddress());
            call.setString(7, hotel.getPhone());
            call.setString(8, hotel.getCity());
            call.setString(9, hotel.getDescription());
            call.executeUpdate();
            call.close();
            return 1;
        } catch (SQLException ex) {
            //Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return 0;
        }
    }

    public boolean update(HotelDTO hotel) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Update_Hotels(?,?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, hotel.getHotelId());
            call.setString(2, hotel.getHotelName());
            call.setString(3, hotel.getEmail());
            call.setString(4, hotel.getWebsite());
            call.setString(5, hotel.getImagesUrl());
            call.setInt(6, hotel.getRate());
            call.setString(7, hotel.getAddress());
            call.setString(8, hotel.getPhone());
            call.setString(9, hotel.getCity());

            call.setString(10, hotel.getDescription());

            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            // Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean delete(HotelDTO hotel) {
        try {
            hotel = new HotelDTO();
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_Hotels(?)}");
            call.setInt(1, hotel.getHotelId());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Delete_Hotels(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            //Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public ArrayList<HotelDTO> findByAllOrther(int id) {
        try {
            con = DatabaseUtil.getConnection();
            ArrayList<HotelDTO> listhotel = new ArrayList<HotelDTO>();
            call = con.prepareCall("SELECT * FROM V_Hotels WHERE HotelId != ?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                hotel = new HotelDTO();
                hotel.setHotelId(rs.getInt(1));
                hotel.setHotelName(rs.getString(2));
                hotel.setEmail(rs.getString(3));
                hotel.setWebsite(rs.getString(4));
                hotel.setImagesUrl(rs.getString(5));
                hotel.setRate(rs.getInt(6));
                hotel.setAddress(rs.getString(7));
                hotel.setPhone(rs.getString(8));
                hotel.setCity(rs.getString(9));
                hotel.setDescription(rs.getString(10));
                listhotel.add(hotel);
            }
            rs.close();
            call.close();
            return listhotel;
        } catch (SQLException ex) {
            //Logger.getLogger(HotelDAO.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<HotelDTO> getHotelCity() {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT Distinct City from V_Hotels");
            rs = call.executeQuery();
            ArrayList<HotelDTO> listCity = new ArrayList<HotelDTO>();
            while (rs.next()) {
                HotelDTO dto = new HotelDTO();
                dto.setCity(rs.getString(1));
                listCity.add(dto);

            }
            return listCity;
        } catch (SQLException ex) {
            return null;

        }

    }
}

